IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPendingItemIds]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetPendingItemIds]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 
/************************************************************************************************                  
* Name:  GetPendingItemIds                                                                    
* Purpose: Stored procedure for retrieving the set of pending items(protocolProcess Ids with status of                  
*  'Pending', 'Deferred', 'Hold', 'Req. Mod.') on Protocols that have status of                  
*  'Active', 'Hold', or 'Hold-Lapsed'                  
*                     
*  This also returns processes that are pending (have status of pending); this means                  
*  they have been submitted to Rex, but have not returned from Rex                  
*                  
* PARAMETERS                    
* Name   Description                  
* ------------------------- -----------------------------                  
* @domainuserid   FwkDomainUserId of the user whose pending items we want to retrieve              
* @module    - module instantiating the page ('H'-Humans, 'A'-Animals)              
* @protocolNo    -Protocol number(Search Criteria)                                
* @protocolTitle   -Protocol Title (Serach criteria)                                
* @sponsorName    -Spnosor name (Search criteria)                                
* @versionNo    -Verson number (Sercah criteria)                                
* @expDateFrom DateTime  -expiry Date From (Search Criteria)                                
* @expDateTo    -Expiry Date To (Search Criteria)                                
* @piId      -PI Id (Search criteria)                                
* @cosIds     -CosIds (Serach Criteria)                                
* @sortColumn    -SortColumn(SubmissionDate-Default)                          
* @sortOrder    -Sort Order for the result set (bit=1-->Desc,0-->Ascending)                              
************************************************************************************************/                  
                  
CREATE PROCEDURE [dbo].[GetPendingItemIds] --116847 ,'H' ,'2007'              
(                  
 @domainuserid int,              
 @module char(1),              
 @protocolNo varchar(12)= null,                                
 @protocolTitle varchar(1024)= null,                                
 @sponsorName varchar(75)= null,                                
 @versionNo varchar(100)= null,                                
 @expDateFrom DateTime = null,                                
 @expDateTo DateTime = null,                                
 @piId Int=null,                                
 @cosIds varchar(Max)= null,          
 @species VarChar(max)= null ,          
 @isRadioIsotope bit = 0 ,          
 @isBiologicalAgent bit = 0 ,          
 @isToxicChemicals bit = 0 ,          
 @isTissueTest bit = 0 ,                               
 @sortColumn varchar(150)= 'SubmissionDate',                                                      
 @sortOrder bit=0-->Asc,0-->Desc                                               
)                  
as                  
begin                  
set nocount on                  
                  
 if  @protocolNo is  null                              
 Set @protocolNo = '%'                              
else                              
 Set @protocolNo = @protocolNo + '%'                              
                              
if  @protocolTitle is null                              
 Set @protocolTitle = '%'                              
else                              
 Set @protocolTitle = @protocolTitle + '%'                              
                              
if @sponsorName is null                              
 Set @sponsorName = '%'                              
Else                               
 Set @sponsorName = @sponsorName + '%'                  
            
if @versionNo is null                              
 Set @versionNo =  '%'                              
Else                               
 Set @versionNo = @versionNo + '%'                                                      
-- constants                  
declare @protocolResourceType int                  
set @protocolResourceType = 2                  
                  
declare @keyGiverAccessLevel int                  
set @keyGiverAccessLevel = 2                  
                  
declare @pendingProcessStatus varchar(256)                  
set @pendingProcessStatus = 'PPSTATUS_PEND'                  
                  
declare @submittedProcessStatus varchar(256)                  
set @submittedProcessStatus = 'PPSTATUS_SUBMT'                  
                  
declare @authorizedProtocols table (ResourceId int, AccessLevel int)             
insert into @authorizedProtocols                   
 select * from fnadmqgetauthorizedresourcelist (@domainuserid, @protocolResourceType)                  
With PendingItems AS                  
    ( SELECT                    
   spon.[name] as 'SponsorName',                    
   fct.ProtocolId ,      
   fct.ProtocolProcessId,                    
   fct.FormsID,                    
   prt.Title,                    
   prt.ProtocolNumber,                    
   pip.lastname + ', ' +  pip.firstname as 'PiName',                    
   fct.SubmissionDate,                    
   fct.BoardName,                    
   fct.ReviewType,                    
   fct.MeetingDate,                    
   fct.BoardStatus,                    
   posty.[Name] as 'OverallStatus',  
   fct.BoardReviewId            
                              
  from @authorizedProtocols ara 
  inner join Protocol prt 
	On ara.ResourceId = fwkResourceId                 
  inner join PendingApplications_Fct fct                  
  on fct.Protocolid = prt.Id                   
  inner join ProtocolOverallStatusType posty                  
  on posty.[id] = prt.[OverallStatusId]                  
  left join AdmPerson pip                  
  on prt.pipersonid = pip.[id]                  
  left join AdmSponsor spon                  
  on prt.Admsponsorid = spon.[id]                  
  where ara.AccessLevel !=  @keyGiverAccessLevel                  
  and prt.moduletype = @module            
  and IsNull(prt.Title,'*') like @protocolTitle                              
  and IsNull(prt.version,'*') like @versionNo                              
  and IsNull(prt.protocolnumber,'*') like @protocolNo                              
  and(DateDiff(dd,cast(IsNull(@expDateFrom, '1/1/1900') as Datetime),cast(IsNull(prt.ExpirationDate,'1/1/1900')as DateTime))>= 0             
  and DateDiff(dd,cast(IsNull(@expDateTo,'12/31/2099') as Datetime),cast(IsNull(prt.ExpirationDate,'1/1/1900') as Datetime))<=0)            
  and IsNull(spon.Name,'*') like @sponsorName                              
  and IsNull(cast(prt.PiPersonId as varchar(20)),'*') like isNull(cast(@piId as varchar(20)),'%')      
  AND IsNull(prt.SpeciesCode , '*') like IsNull(@species,'%')              
  AND (prt.RadioIsotope = @isRadioIsotope            
        or 0 = @isRadioIsotope)            
  AND (prt.BiologicalAgent = @isBiologicalAgent            
        or 0 = @isBiologicalAgent)            
  AND (prt.ToxicChem = @isToxicChemicals            
        or 0 = @isToxicChemicals)            
  AND (prt.TissueTest = @isTissueTest            
        or 0 = @isTissueTest)
  AND IsNull(fct.BoardStatus, 'Pending') <> 'Approved'                                  
union                        
  select               
   spon.[name] as 'SponsorName',                    
   pp.ProtocolId ,      
   pp.Id as 'ProtocolProcessId',                    
   pp.[Name] as 'FormsID',                    
   prt.Title,                    
   prt.ProtocolNumber,                    
   pip.lastname + ', ' +  pip.firstname as 'PiName',                    
   pp.ReceivedDate as 'SubmissionDate', -- todo: this will be a field on the protocolProcess at some point                    
   null as 'BoardName',                    
   null as 'ReviewType',                    
   null as 'MeetingDate',                    
   null as 'BoardStatus',                    
   'Pending' as 'OverallStatus',  
   null as 'BoardReviewId'  
                             
  from @authorizedProtocols ara                   
  inner join Protocol prt                  
  on ara.Resourceid = prt.Fwkresourceid                   
  inner join ProtocolProcess pp                  
  on pp.ProtocolId = prt.[Id]                  
  left join AdmSponsor spon                  
  on prt.Admsponsorid = spon.[id]                  
  left join AdmPerson pip                  
  on prt.pipersonid = pip.[id]                  
                    
  where                  
  ara.AccessLevel !=  @keyGiverAccessLevel                  
  AND (pp.Status = @pendingProcessStatus  OR pp.Status = @submittedProcessStatus)                  
  AND prt.MarkForDeletion = 0              
  AND prt.moduletype = @module                  
  -- Pending Processes that have already made it to Rex and back will be in the ProtocolSubmission                  
  -- table as well, and we don't want both records to show up                  
  AND not exists (Select ProtocolProcessId from ProtocolSubmission where ProtocolProcessId = pp.[Id])                  
  AND IsNull(prt.Title,'*') like @protocolTitle                              
  AND IsNull(prt.version,'*') like @versionNo                              
  AND IsNull(prt.protocolnumber,'*') like @protocolNo                              
  AND(DateDiff(dd,cast(IsNull(@expDateFrom, '1/1/1900') as Datetime),cast(IsNull(prt.ExpirationDate,'1/1/1900')as DateTime))>= 0             
  AND DateDiff(dd,cast(IsNull(@expDateTo,'12/31/2099') as Datetime),cast(IsNull(prt.ExpirationDate,'1/1/1900') as Datetime))<=0)            
  AND IsNull(spon.Name,'*') like @sponsorName                              
  AND IsNull(cast(prt.PiPersonId as varchar(20)),'*') like isNull(cast(@piId as varchar(20)),'%')      
  AND IsNull(prt.SpeciesCode , '*') like IsNull(@species,'%')              
  AND (prt.RadioIsotope = @isRadioIsotope            
        or 0 = @isRadioIsotope)            
  AND (prt.BiologicalAgent = @isBiologicalAgent            
        or 0 = @isBiologicalAgent)            
  AND (prt.ToxicChem = @isToxicChemicals            
        or 0 = @isToxicChemicals)            
  AND (prt.TissueTest = @isTissueTest            
        or 0 = @isTissueTest)                                
  )            
 Select ProtocolId,ProtocolProcessId,BoardReviewId from PendingItems             
 Order by             
  Case @sortOrder                             
     When 0 then                                
      CASE @SortColumn                               
    WHEN 'Title' THEN [Title]                                        
    When 'PIName' Then [piName]                              
    When 'SponsorName' Then [SponsorName]            
    When 'OverallStatus' Then OverallStatus                                     
    When 'ProtocolNumber' THEN [ProtocolNumber]            
    When 'FormsId'   Then [FormsId]                           
    When 'BoardStatus'   Then [BoardStatus]            
    When 'ReviewType' Then [ReviewType]            
    When 'BoardName' Then [BoardName]                
    END                      
       END Desc,                       
    CASE @SortOrder                              
      When 0 then                        
      CASE @SortColumn                               
    When 'SubmissionDate' Then [SubmissionDate]                               
    When 'MeetingDate' Then [MeetingDate]            
    ELSE [SubmissionDate]                        
    END                                   
    END Desc,                              
    CASE @SortOrder                              
    When 1 then                               
      CASE @SortColumn                               
    WHEN 'Title' THEN [Title]                                        
    When 'PIName' Then [piName]                              
    When 'SponsorName' Then [SponsorName]            
    When 'OverallStatus' Then OverallStatus                                                                                             
    When 'ProtocolNumber' THEN [ProtocolNumber]            
    When 'FormsId'   Then [FormsId]                           
    When 'BoardStatus'   Then [BoardStatus]            
    When 'ReviewType' Then [ReviewType]            
    When 'BoardName' Then [BoardName]                                
    END                        
    END ASC,                      
    CASE @SortOrder                        
     When 1 then                        
      case @SortColumn                                
    When 'SubmissionDate' Then [SubmissionDate]                               
    When 'MeetingDate' Then [MeetingDate]            
    ELSE [SubmissionDate]                        
       END                                  
    END                  
              
end                  
                  